package com.bw.mapper;

import com.bw.bean.Vo;
import com.bw.bean.Vo1;
import com.bw.bean.Vo2;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface VoMapper {
    @Select("select hour(create_time) 'name' ,count(*) 'value'\n" +
        "from user_chapter_process group by hour(create_time)\n" +
        "order by hour(create_time)")
    List<Vo> getlist1();
@Select("select ci.course_name 'name',count(distinct te.user_id) 'num',\n" +
        "       avg(te.score) 'avgscore',avg(te.duration_sec) 'avgtime'\n" +
        "from test_exam te\n" +
        "left join test_paper tp on tp.id = te.paper_id\n" +
        "left join course_info ci on ci.id = tp.course_id\n" +
        "group by ci.course_name")
    List<Vo1> getlist2();
@Select("select count(*)\n" +
        "from payment_info")
    Double getlist3();
@Select("select ci.course_name 'name',avg(re.review_stars) 'avgnum',count( distinct re.user_id) 'num',\n" +
        "       sum(if(review_stars>=3,1,0)) 'num1',\n" +
        "       sum(if(review_stars>=3,1,0))/count( distinct re.user_id) 'hp'\n" +
        "from  review_info re\n" +
        "left join course_info ci on ci.id = re.course_id\n" +
        "group by ci.course_name")
    List<Vo2> getlist4();
@Select("select  tqi.question_txt 'name',count(te.id) 'num',\n" +
        "        sum(te.is_correct) 'avgscore',sum(te.is_correct)/count(te.id) 'avgtime'\n" +
        "from test_exam_question te\n" +
        "left join test_question_info tqi on tqi.id = te.question_id\n" +
        "group by tqi.question_txt")
    List<Vo1> getlist5();
@Select("select tp.paper_title 'name', count(distinct  te.user_id) 'num',\n" +
        "       avg(te.score) 'avgscore',avg(te.duration_sec) 'avgtime'\n" +
        "from test_exam te left join test_paper tp on tp.id = te.paper_id\n" +
        "group by tp.paper_title")
    List<Vo1> getlist6();
@Select("select bs.source_site 'name' ,sum(oi.final_amount) 'value' from order_info oi\n" +
        "left join order_detail od on oi.id = od.order_id\n" +
        "left join course_info ci on ci.id = od.course_id\n" +
        "left join chapter_info c on ci.id = c.course_id\n" +
        "left join video_info vi on c.id = vi.chapter_id\n" +
        "left join base_source bs on bs.id = vi.video_source_id\n" +
        "group by  bs.source_site")
    List<Vo> getlist7();
}
